package com.topisv.tms.service.work;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.hibernate.ejb.criteria.predicate.ComparisonPredicate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.fasterxml.jackson.annotation.JsonTypeInfo.Id;
import com.fasterxml.jackson.databind.ObjectMapper;
// import com.topisv.tms.api.TaoBaoAPITool;
import com.topisv.tms.entity.AreaGroup;
import com.topisv.tms.entity.Customers;
import com.topisv.tms.entity.EdiOrder;
import com.topisv.tms.entity.EdiOrderItem;
import com.topisv.tms.entity.QueryParamHolder;
import com.topisv.tms.entity.User;
import com.topisv.tms.entity.work.WorkBatch;
import com.topisv.tms.entity.work.WorkOrder;
import com.topisv.tms.entity.work.WorkTask;
import com.topisv.tms.entity.work.WorkTaskItem;
import com.topisv.tms.repository.AreaGroupDao;
import com.topisv.tms.repository.CustomersDao;
import com.topisv.tms.repository.EdiOrderDao;
import com.topisv.tms.repository.EdiOrderItemDao;
import com.topisv.tms.repository.UserDao;
import com.topisv.tms.repository.log.OrderLogDao;
import com.topisv.tms.repository.work.WholePileConfigDao;
import com.topisv.tms.repository.work.WorkBatchDao;
import com.topisv.tms.repository.work.WorkLogDao;
import com.topisv.tms.repository.work.WorkOrderDao;
import com.topisv.tms.repository.work.WorkTaskDao;
import com.topisv.tms.repository.work.WorkTaskItemDao;
import com.topisv.tms.rest.entity.Batch;
import com.topisv.tms.rest.entity.Box;
import com.topisv.tms.rest.entity.Box_detail;
import com.topisv.tms.rest.entity.Order;
import com.topisv.tms.rest.qimen.qmsend.QmRequestSender;
import com.topisv.tms.rest.qimen.qmservice.DeliveryProcessService;
import com.topisv.tms.service.account.ShiroDbRealm.ShiroUser;
import com.topisv.tms.service.base.BaseService;
import com.topisv.tms.service.interfacetask.InterfaceTaskService;
import com.topisv.tms.tools.TransferHelper;
import com.topisv.tms.utils.DateUtils;
import com.topisv.tms.utils.TimeUtil;
import com.topisv.tms.utils.rule.TMSTransportRule;

// import com.topisv.tms.utils.http.SynchroShippedBatchQueue;

/**
 * 作业批次_service
 * 批次创建订单for双十一
 * @author ch
 */
@Component
@Deprecated
@Transactional(readOnly = true)
public class WorkBatchServiceForBatch extends BaseService {
    
    @Autowired
    private WorkBatchDao workBatchDao;
    
    @Autowired
    private WorkTaskDao workTaskDao;
    
    @Autowired
    private WorkTaskItemDao workTaskItemDao;
    
    @Autowired
    private EdiOrderDao ediOrderDao;
    
    @Autowired
    private UserDao userDao;
    
    @Autowired
    private WorkLogDao workLogDao;
    
    @Autowired
    private OrderLogDao orderLogDao;
    
    @Autowired
    private WorkOrderDao workOrderDao;
    
    @Autowired
    private CustomersDao customersDao;
    
    @Autowired
    private AreaGroupDao areaGroupDao;
    
    @Autowired
    private EdiOrderItemDao ediOrderItemDao;
    
    @Resource(type = DeliveryProcessService.class)
    QmRequestSender<Map<String, Object>> deliveryProcessService;
    
    @Autowired
    private WholePileConfigDao wpcDao;
    
    @Autowired
    private InterfaceTaskService interfaceTaskService;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 创建作业批次
     * 
     * @param orderNum
     *            单个批次的订单数量限制
     * @param customerCode
     *            商家代码
     * @param warehouseCode
     *            作业仓库
     * @param ordersArray
     *            订单数组
     * @param pickType
     *            订单类型 （single:单件 multi:多件）
     * @throws Exception
     */
    @Deprecated
    //@Transactional(readOnly = false, rollbackFor = Exception.class)
    public void createWorkBatchBak(String tacticsType, Integer orderNum, String customerCode, String companyCode,
        String warehouseCode, String[] ordersArray, String pickType, String billType, String flowType, String psFlag)
        throws Exception {
        try {
            ShiroUser user = (ShiroUser)SecurityUtils.getSubject().getPrincipal();
            User curUser = this.userDao.findOne(user.id);
            
            ArrayList<String> batchSql=new ArrayList<String>();
            ArrayList<String> workLogSql=new ArrayList<String>();
            ArrayList<String> orderLogSql=new ArrayList<String>();
            ArrayList<String> workOrderSql=new ArrayList<String>();
            
            int tmpi=0;
            int tmpj=0;
            int tmpT=ordersArray.length;
            String tmpSql = getSqlParamOfOrders(ordersArray);
            
            List<Map<String, Object>> reDoMap = getreDoData(tmpSql);
            if (reDoMap.size()>0) {
                throw new Exception("重复创建批次，订单号：" + reDoMap.get(0).get("orderno"));
            }
            
            List<Map<String, Object>> ediOrderItemMap = getSkuAndQtyFromEdiOrderItem(tmpSql);
            Map<String, String> ediOrderItemSku = new HashMap<String, String>();
            Map<String, Integer> ediOrderItemQty = new HashMap<String, Integer>();
            
            for (Map<String, Object> map : ediOrderItemMap) {
                ediOrderItemSku.put((String)map.get("ordeno"), (String)map.get("sku"));
                ediOrderItemQty.put((String)map.get("ordeno"), Integer.parseInt(map.get("qty").toString()));
            }
            
            List<Map<String, Object>> tmpMap = getSkuQtyFromEdiOrderItem(tmpSql);
            Map<String, Map<String, Integer>> goodInfoMap = new HashMap<String, Map<String,Integer>>();
            Map<String, Integer> orderItemMap = new HashMap<String, Integer>();
            
            for (Map<String, Object> map : tmpMap) {
                String tmpOrder=(String)map.get("ordeno");
                
                if (goodInfoMap.containsKey(tmpOrder)) {
                    goodInfoMap.get("tmpOrder").put((String)map.get("sku"), Integer.parseInt(map.get("qty").toString()));
                } else {
                    orderItemMap.clear();
                    orderItemMap.put((String)map.get("sku"), Integer.parseInt(map.get("qty").toString()));
                    goodInfoMap.put(tmpOrder, orderItemMap);
                }
            }
            
            String sku_last = "";
            
            for (int i = 0; i < ordersArray.length; i++) {
                tmpi +=1;
                String orderno = ordersArray[i];
                String sku = ediOrderItemSku.get(ordersArray[i]); 
                String batchCode = TMSTransportRule.generateByPreFix("B" + customerCode);
                
                if (("onesku".equals(tacticsType) && "single".equals(pickType)) || "promotion".equals(tacticsType)) {
                    if (!sku_last.equals(sku)) {
                        sku_last = sku;
                        // 创建新的批次
                        batchSql.add("insert into work_batch(batch_type,create_time,customer_code,company_code,warehouse_code,creater_id,pick_type,status,"
                                   +                        "bill_type,flow_type,tactics_type,priority_send_flag,batch_code,order_qty) VALUES ("
                                   + "'" + curUser.getUserType() + "','" + DateUtils.getCurrentDateHHMM() + "','" + customerCode + "','" + companyCode + "','" + warehouseCode + "',"
                                   + "" + user.id + ",'" + pickType + "','1','" + billType + "','" + flowType + "','" + tacticsType + "','" + psFlag + "','"+ batchCode +"',"+ tmpi +")");
                        
                        // 工作日志
                        workLogSql.add("insert into work_log(batch_code,work_type,info,work_time,worker_id) VALUES ("
                                     + "'" + batchCode + "','1','" + user.getRealname() + "创建作业批次(" + batchCode + ")" + "','" + DateUtils.getCurrentDateHHMM() + "'," + user.id + ")");
                        tmpi=0;
                    }
                } else {
                    if (i % orderNum == 0) {
                        if(tmpT>orderNum){
                            tmpj=orderNum;
                            tmpT-=orderNum;
                        }else {
                            tmpj=tmpT;
                        }
                        // 创建新的批次
                        batchSql.add("insert into work_batch(batch_type,create_time,customer_code,company_code,warehouse_code,creater_id,pick_type,status,"
                                   +                        "bill_type,flow_type,tactics_type,priority_send_flag,batch_code,order_qty) VALUES ("
                                   + "'" + curUser.getUserType() + "','" + DateUtils.getCurrentDateHHMM() + "','" + customerCode + "','" + companyCode + "','" + warehouseCode + "',"
                                   + "" + user.id + ",'" + pickType + "','1','" + billType + "','" + flowType + "','" + tacticsType + "','" + psFlag + "','"+ batchCode +"',"+ tmpj +")");
                        
                        // 工作日志
                        workLogSql.add("insert into work_log(batch_code,work_type,info,work_time,worker_id) VALUES ("
                                     + "'" + batchCode + "','1','" + user.getRealname() + "创建作业批次(" + batchCode + ")" + "','" + DateUtils.getCurrentDateHHMM() + "'," + user.id + ")");
                        
                        log.info(user.getRealname() + "创建作业批次(" + batchCode + ")");
                    }
                }
                
                // 关联批次和订单数据
                workOrderSql.add("insert into work_order(create_time,batch_code,customer_code,warehouse_code,orderno,"
                                + "status,bill_type,flow_type,ebill_status,cancel_status,pkg_lock,priority_send_flag,goods_info,goods_num,rankno) VALUES ("
                                + "'"+ DateUtils.getCurrentDateHHMM() +"','"+ batchCode +"',"
                                + "'"+ customerCode +"','"+ warehouseCode +"','"+ orderno +"','1','"+ billType +"','"+ flowType +"',"
                                + "'uncreate','ok','unpkg','"+ psFlag +"','"+ getGoodsInfoNew(goodInfoMap.get(orderno)) +"',"+ ediOrderItemQty.get(orderno) +",0)");
                
                // 订单日志
                orderLogSql.add("insert into order_log(orderno,customer_code,operate_type,operater_jobuuid,operate_time,about_info) VALUES ("
                              + "'" + orderno + "','" + customerCode + "','1','" + user.jobuuid + "','" + DateUtils.getCurrentDateHHMM() + "','" + curUser.getRealname() + "创建作业批次" + "')");
                
            }
            
            batchInsert((String [])batchSql.toArray(new String[batchSql.size()]));
            batchInsert((String [])workLogSql.toArray(new String[workLogSql.size()]));
            batchInsert((String [])orderLogSql.toArray(new String[orderLogSql.size()]));
            batchInsert((String [])workOrderSql.toArray(new String[workOrderSql.size()]));
            //TODO事务问题 可能导致锁等待
            updateForCreateBatch("update work_order a,edi_order b set a.need_invoice=case b.is_invoice when 0 then '1' else '0' end,"
                + "a.customer_orderno = b.customer_ordeno,a.fromno = b.fromno where a.orderno=b.ordeno and a.orderno in (" + tmpSql + ")");
            //修改订单状态
            updateForCreateBatch("update edi_order a,work_order b set a.status = 'finish',a.message = 'sf',a.batch_task_code = b.batch_code "
                + " where a.ordeno=b.orderno and a.ordeno in (" + tmpSql + ")");
            
            sendQMCreateBatch(tmpSql);
            
        } catch (Exception e) {
            log.error("-----发送奇门出库单流水信息发送完毕 仓库接单 error", e);
            throw e;
        }
        
    }
    
    /**
     * 创建作业批次
     * 
     * @param orderNum
     *            单个批次的订单数量限制
     * @param customerCode
     *            商家代码
     * @param warehouseCode
     *            作业仓库
     * @param ordersArray
     *            订单数组
     * @param pickType
     *            订单类型 （single:单件 multi:多件）
     * @throws Exception
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public void createWorkBatch(String tacticsType, Integer orderNum, String customerCode, String companyCode,
        String warehouseCode, String[] ordersArray, String pickType, String billType, String flowType, String psFlag)
        throws Exception {
        try {
            ShiroUser user = (ShiroUser)SecurityUtils.getSubject().getPrincipal();
            User curUser = this.userDao.findOne(user.id);
            
            ArrayList<String> batchSql=new ArrayList<String>();
            ArrayList<String> workLogSql=new ArrayList<String>();
            ArrayList<String> orderLogSql=new ArrayList<String>();
            ArrayList<String> workOrderSql=new ArrayList<String>();
            
            List<EdiOrder> orderList=new ArrayList<EdiOrder>();
            
            int tmpi=0;
            int tmpj=0;
            int tmpT=ordersArray.length;
            String tmpSql = getSqlParamOfOrders(ordersArray);
            
            List<Map<String, Object>> reDoMap = getreDoData(tmpSql);
            if (reDoMap.size()>0) {
                throw new Exception("重复创建批次，订单号：" + reDoMap.get(0).get("orderno"));
            }
            
            //计算一个订单的SKU总数
            List<Map<String, Object>> ediOrderItemMap = getSkuAndQtyFromEdiOrderItem(tmpSql);
            Map<String, String> ediOrderItemSku = new HashMap<String, String>();
            Map<String, Integer> ediOrderItemQty = new HashMap<String, Integer>();
            for (Map<String, Object> map : ediOrderItemMap) {
                ediOrderItemSku.put((String)map.get("ordeno"), (String)map.get("sku"));
                ediOrderItemQty.put((String)map.get("ordeno"), Integer.parseInt(map.get("qty").toString()));
            }
            
            //统计订单明细的SKU和数量,为了给订单的goods_info赋值
            List<Map<String, Object>> tmpMap = getSkuQtyFromEdiOrderItem(tmpSql);
            Map<String, Map<String, Integer>> goodInfoMap = new HashMap<String, Map<String,Integer>>();
            for (Map<String, Object> map : tmpMap) {
                Map<String, Integer> orderItemMap = new HashMap<String, Integer>();
                String tmpOrder=(String)map.get("ordeno");
                if (goodInfoMap.containsKey(tmpOrder)) {
                    goodInfoMap.get(tmpOrder).put((String)map.get("sku"), Integer.parseInt(map.get("qty").toString()));
                } else {
                    orderItemMap.put((String)map.get("sku"), Integer.parseInt(map.get("qty").toString()));
                    goodInfoMap.put(tmpOrder, orderItemMap);
                }
            }
            
            String sku_last = "";
            String batchCode ="";
            for (int i = 0; i < ordersArray.length; i++) {
                String orderno = ordersArray[i];
                String sku = ediOrderItemSku.get(ordersArray[i]); 
                if (("onesku".equals(tacticsType) && "single".equals(pickType)) || "promotion".equals(tacticsType)) {
                    tmpi +=1;
                    if (!sku_last.equals(sku)) {
                        sku_last = sku;
                        if (i>0) {
                            // 创建新的批次
                            batchSql.add("insert into work_batch(batch_type,create_time,customer_code,company_code,warehouse_code,creater_id,pick_type,status,"
                                       +                        "bill_type,flow_type,tactics_type,priority_send_flag,batch_code,order_qty) VALUES ("
                                       + "'" + curUser.getUserType() + "','" + DateUtils.getCurrentDateHHMM() + "','" + customerCode + "','" + companyCode + "','" + warehouseCode + "',"
                                       + "" + user.id + ",'" + pickType + "','1','" + billType + "','" + flowType + "','" + tacticsType + "','" + psFlag + "','"+ batchCode +"',"+ tmpi +")");
                            
                            // 工作日志
                            workLogSql.add("insert into work_log(batch_code,work_type,info,work_time,worker_id) VALUES ("
                                         + "'" + batchCode + "','1','" + user.getRealname() + "创建作业批次(" + batchCode + ")" + "','" + DateUtils.getCurrentDateHHMM() + "'," + user.id + ")");
                        }
                        batchCode = TMSTransportRule.generateByPreFixCut("B" + customerCode,5);
                        tmpi=0;
                    }
                    if(i == ordersArray.length-1){
                        // 创建新的批次
                        batchSql.add("insert into work_batch(batch_type,create_time,customer_code,company_code,warehouse_code,creater_id,pick_type,status,"
                            +                        "bill_type,flow_type,tactics_type,priority_send_flag,batch_code,order_qty) VALUES ("
                            + "'" + curUser.getUserType() + "','" + DateUtils.getCurrentDateHHMM() + "','" + customerCode + "','" + companyCode + "','" + warehouseCode + "',"
                            + "" + user.id + ",'" + pickType + "','1','" + billType + "','" + flowType + "','" + tacticsType + "','" + psFlag + "','"+ batchCode +"',"+ (tmpi+1) +")");
                        
                        // 工作日志
                        workLogSql.add("insert into work_log(batch_code,work_type,info,work_time,worker_id) VALUES ("
                            + "'" + batchCode + "','1','" + user.getRealname() + "创建作业批次(" + batchCode + ")" + "','" + DateUtils.getCurrentDateHHMM() + "'," + user.id + ")");
                    }
                    
                    
                } else {
                    if (i % orderNum == 0) {
                        if(tmpT>orderNum){
                            tmpj=orderNum;
                            tmpT-=orderNum;
                        }else {
                            tmpj=tmpT;
                        }
                        batchCode = TMSTransportRule.generateByPreFixCut("B" + customerCode,5);
                        // 创建新的批次
                        batchSql.add("insert into work_batch(batch_type,create_time,customer_code,company_code,warehouse_code,creater_id,pick_type,status,"
                                   +                        "bill_type,flow_type,tactics_type,priority_send_flag,batch_code,order_qty) VALUES ("
                                   + "'" + curUser.getUserType() + "','" + DateUtils.getCurrentDateHHMM() + "','" + customerCode + "','" + companyCode + "','" + warehouseCode + "',"
                                   + "" + user.id + ",'" + pickType + "','1','" + billType + "','" + flowType + "','" + tacticsType + "','" + psFlag + "','"+ batchCode +"',"+ tmpj +")");
                        
                        // 工作日志
                        workLogSql.add("insert into work_log(batch_code,work_type,info,work_time,worker_id) VALUES ("
                                     + "'" + batchCode + "','1','" + user.getRealname() + "创建作业批次(" + batchCode + ")" + "','" + DateUtils.getCurrentDateHHMM() + "'," + user.id + ")");
                        
                        log.info(user.getRealname() + "创建作业批次(" + batchCode + ")");
                    }
                }
                
                EdiOrder order=this.ediOrderDao.findByOrdeno(orderno);
                
                // 关联批次和订单数据
                workOrderSql.add("insert into work_order(create_time,batch_code,need_invoice,customer_orderno,fromno,customer_code,warehouse_code,orderno,"
                                + "status,bill_type,flow_type,ebill_status,cancel_status,pkg_lock,priority_send_flag,goods_info,goods_num,rankno) VALUES ("
                                + "'"+ DateUtils.getCurrentDateHHMM() +"','"+ batchCode +"','" + TransferHelper.boolean2String(order.getIsInvoice()) + "',"
                                + "'" + order.getCustomerOrdeno() + "','" + order.getFromno() + "',"
                                + "'"+ customerCode +"','"+ warehouseCode +"','"+ orderno +"','1','"+ billType +"','"+ flowType +"',"
                                + "'uncreate','ok','unpkg','"+ psFlag +"','"+ getGoodsInfoNew(goodInfoMap.get(orderno)) +"',"+ ediOrderItemQty.get(orderno) +",0)");
                
                // 订单日志
                orderLogSql.add("insert into order_log(orderno,customer_code,operate_type,operater_jobuuid,operate_time,about_info) VALUES ("
                              + "'" + orderno + "','" + customerCode + "','1','" + user.jobuuid + "','" + DateUtils.getCurrentDateHHMM() + "','" + curUser.getRealname() + "创建作业批次" + "')");
                
                order.setStatus("finish"); //订单状态   (finish:生成批次   pending:准备状态)
                order.setMessage("sf");
                order.setBatchTaskCode(batchCode);
                orderList.add(order);
            }
            
            batchInsert((String [])batchSql.toArray(new String[batchSql.size()]));
            batchInsert((String [])workLogSql.toArray(new String[workLogSql.size()]));
            batchInsert((String [])orderLogSql.toArray(new String[orderLogSql.size()]));
            batchInsert((String [])workOrderSql.toArray(new String[workOrderSql.size()]));
            
            this.ediOrderDao.save(orderList);
            
            sendQMCreateBatch(tmpSql);
            
        } catch (Exception e) {
            log.error("-----发送奇门出库单流水信息发送完毕 仓库接单 error", e);
            throw e;
        }
        
    }
    /**
     * 获取查询的条件
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    public String getSqlParamOfOrders(String[] ordersArray) {
        StringBuffer sqlBuffer=new StringBuffer();
        for (String s : ordersArray) {
            sqlBuffer.append("'").append(s).append("'").append(",");
        }
        sqlBuffer.append("''");
        String tmpSql = sqlBuffer.toString();
        return tmpSql;
    }
    
    /**
     * 获取商品需求字符串
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    public String getGoodsInfoNew(Map<String, Integer> orderItemMap) {
        StringBuilder sb = new StringBuilder();
        // 排序
        List<Map.Entry<String, Integer>> mappingList =
            new ArrayList<Map.Entry<String, Integer>>(orderItemMap.entrySet());
        Collections.sort(mappingList, new Comparator<Map.Entry<String, Integer>>() {
            
            public int compare(Map.Entry<String, Integer> mapping1, Map.Entry<String, Integer> mapping2) {
                return mapping1.getKey().compareTo(mapping2.getKey());
            }
        });
        // 生成字符串
        for (Map.Entry<String, Integer> mapping : mappingList) {
            String key = mapping.getKey();
            
            if (sb.length() > 0) {
                sb.append(";");
            }
            sb.append(key).append(":").append(mapping.getValue());
        }
        
        return sb.toString();
    }
    
    /**
     * 批次处理指定的SQL语句
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    @Transactional(rollbackFor=Exception.class,readOnly = false)
    public int[] batchInsert(String [] sql) throws Exception{
        log.info("batchInsert--sql:{}",sql[0]);
        return jdbcTemplate.batchUpdate(sql);
    }
    
    /**
     * 查询订单明细的数量
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    public List<Map<String, Object>> getSkuAndQtyFromEdiOrderItem(String sqlParam) throws Exception{
        String sql="select ordeno,sum(qty) as qty,max(sku_id) as sku from edi_order_item WHERE ordeno in(" + sqlParam + ") GROUP BY ordeno";
        return jdbcTemplate.queryForList(sql);
    }
    
    /**
     * 创建批次，发送奇门接口
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    @Transactional(rollbackFor=Exception.class,readOnly = false)
    public int sendQMCreateBatch(String sqlParam) throws Exception{
        String sql="insert into interface_task(type,related_no,related_id,created_time,retry_times,error_who,error_msg,status,node)" 
                   + " (select '奇门出库单流水接单',ordeno,id,'" + DateUtils.getCurrentDateHHMM() + "',0,'-','-','READY','-' "
                   + " from edi_order where ordeno in(" + sqlParam + "))";
        return jdbcTemplate.update(sql);
    }
    
    /**
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    @Transactional(rollbackFor=Exception.class,readOnly = false)
    public int updateForCreateBatch(String sql) throws Exception{
        return jdbcTemplate.update(sql);
    }
    
    /**
     * 查询订单明细的sku的数量
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    public List<Map<String, Object>> getSkuQtyFromEdiOrderItem(String sqlParam) throws Exception{
        String sql="select ordeno,sku_id as sku,sum(qty) as qty from edi_order_item WHERE ordeno in(" + sqlParam + ") GROUP BY ordeno,sku_id";
        return jdbcTemplate.queryForList(sql);
    }
    
    /**
     * 查询重复创建批次的订单
     * for双十一
     * @param 
     * @return 
     * @throws 
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 1.0.0
     */
    public List<Map<String, Object>> getreDoData(String sqlParam) throws Exception{
        String sql="select orderno from work_order WHERE orderno in(" + sqlParam + ") and cancel_status='ok' ";
        return jdbcTemplate.queryForList(sql);
    }
    
    /**
     * 
     * @param workBatch
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public void updateBatch(WorkBatch workBatch) {
        this.workBatchDao.updateSyncStatus(workBatch.getBatchCode(), workBatch.getSyncStatus());
    }
    
    /**
     * 获取商品需求字符串
     * 
     * @param orderno
     *            订单号
     * @return
     */
    public String getGoodsInfo(String orderno, List<EdiOrderItem> orderItemList) {
        StringBuilder sb = new StringBuilder();
        
        // 获取map
        if (null == orderItemList) {
            orderItemList = this.ediOrderItemDao.findByOrdeno(orderno);
        }
        Map<String, Integer> orderItemMap = new HashMap<String, Integer>();
        for (EdiOrderItem orderItem : orderItemList) {
            String sku = orderItem.getSku();
            int num = orderItem.getQty();
            if (orderItemMap.containsKey(sku)) {
                orderItemMap.put(sku, orderItemMap.get(sku) + num);
            } else {
                orderItemMap.put(sku, num);
            }
        }
        
        // 排序
        List<Map.Entry<String, Integer>> mappingList =
            new ArrayList<Map.Entry<String, Integer>>(orderItemMap.entrySet());
        Collections.sort(mappingList, new Comparator<Map.Entry<String, Integer>>() {
            
            public int compare(Map.Entry<String, Integer> mapping1, Map.Entry<String, Integer> mapping2) {
                return mapping1.getKey().compareTo(mapping2.getKey());
            }
        });
        
        // 生成字符串
        for (Map.Entry<String, Integer> mapping : mappingList) {
            String key = mapping.getKey();
            
            if (sb.length() > 0) {
                sb.append(";");
            }
            sb.append(key).append(":").append(mapping.getValue());
        }
        
        return sb.toString();
    }
    
    /**
     * 查找作业批次
     * 
     * @param param
     *            查询参数
     * @return
     */
    public List<WorkBatch> loadWorkBatch(Map<String, Object> param) {
        Specification<WorkBatch> spec = getWhereClause(param);
        Sort sort = new Sort(Direction.DESC, "id");
        List<WorkBatch> workBatchList = this.workBatchDao.findAll(spec, sort);
        Map<String, Object> cacheMap = new HashMap<String, Object>();
        for (WorkBatch workBatch : workBatchList) {
            // 换成使用缓存
            this.setDetail(workBatch, cacheMap);
            // this.setDetail(workBatch);
        }
        return workBatchList;
    }
    
    /**
     * 分页查询方式，组合特定的查询条件
     * 
     * @param
     * @return
     * @author <a href=mailto:libin@nryuncang.com>libin</a>
     * @version 0.0.16
     */
    public Page<WorkBatch> loadPageWorkBatch(Map<String, Object> param, int pageNumber, int pageSize) {
        
        Sort sort = new Sort(Direction.DESC, "id");
        PageRequest pageRequest = new PageRequest(pageNumber - 1, pageSize, sort);
        Specification<WorkBatch> spec = getWhereClause(param);
        Page<WorkBatch> page = workBatchDao.findAll(spec, pageRequest);
        
        Map<String, Object> cacheMap = new HashMap<String, Object>();
        for (WorkBatch workBatch : page) {
            this.setDetail(workBatch, cacheMap);
        }
        
        return page;
    }
    
    /**
     * 分页
     * 
     * @param paramHolder
     * @return
     */
    public Page<WorkBatch> PageWorkBatch(QueryParamHolder paramHolder) {
        Page<WorkBatch> workBatchs = findByJson(paramHolder, workBatchDao, WorkBatch.class);
        return workBatchs;
    }
    
    /**
     * 查询条件封装
     * 
     * @param searchParams
     * @return
     */
    private Specification<WorkBatch> getWhereClause(final Map<String, Object> searchParams) {
        
        return new Specification<WorkBatch>() {
            
            @Override
            public Predicate toPredicate(Root<WorkBatch> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
                Predicate pre = cb.conjunction();
                
                // customerCodes
                String customerCodes = (String)searchParams.get("customerCodes");
                if (StringUtils.isBlank(customerCodes)) {
                    customerCodes = "blank";
                }
                String[] customerCodeSplit = customerCodes.split(":");
                ComparisonPredicate[] tempCustomerCode = new ComparisonPredicate[customerCodeSplit.length];
                int i = 0;
                for (String _customerCode : customerCodeSplit) {
                    tempCustomerCode[i] = (ComparisonPredicate)cb.equal(r.get("customerCode"), _customerCode);
                    i++;
                }
                pre.getExpressions().add(cb.or(tempCustomerCode));
                
                // areagroups
                String areagroups = (String)searchParams.get("areagroups");
                if (StringUtils.isBlank(areagroups)) {
                    areagroups = "blank";
                }
                String[] areagroupSplit = areagroups.split(":");
                ComparisonPredicate[] areagroupArray = new ComparisonPredicate[areagroupSplit.length];
                int j = 0;
                for (String _areagroup : areagroupSplit) {
                    areagroupArray[j] = (ComparisonPredicate)cb.equal(r.get("warehouseCode"), _areagroup);
                    j++;
                }
                pre.getExpressions().add(cb.or(areagroupArray));
                
                // createTime
                Date fromDate = (Date)searchParams.get("fromDate");
                if (fromDate != null) {
                    pre.getExpressions().add(cb.greaterThanOrEqualTo(r.<Date> get("createTime"), fromDate));
                }
                
                Date toDate = (Date)searchParams.get("toDate");
                if (toDate != null) {
                    pre.getExpressions().add(cb.lessThanOrEqualTo(r.<Date> get("createTime"), toDate));
                }
                
                // status
                String status = (String)searchParams.get("status");
                if (StringUtils.isNotBlank(status)) {
                    pre.getExpressions().add(cb.equal(r.get("status"), status));
                }
                
                // pickType
                String pickType = (String)searchParams.get("pickType");
                if (StringUtils.isNotBlank(pickType)) {
                    pre.getExpressions().add(cb.equal(r.get("pickType"), pickType));
                }
                
                // batchCode
                String batchCode = (String)searchParams.get("batchCode");
                if (StringUtils.isNotBlank(batchCode)) {
                    pre.getExpressions().add(cb.equal(r.get("batchCode"), batchCode.trim()));
                }
                String companyCode = (String)searchParams.get("companyCode");
                if (StringUtils.isNotBlank(companyCode)) {
                    pre.getExpressions().add(cb.equal(r.get("companyCode"), companyCode.trim()));
                }
                
                // 优先派送标志
                String psFlag = (String)searchParams.get("psFlag");
                if (StringUtils.isNotBlank(psFlag)) {
                    if ("-1".equals(psFlag)) {
                        ComparisonPredicate[] prioritySendFlagArray = new ComparisonPredicate[2];
                        prioritySendFlagArray[0] = (ComparisonPredicate)cb.equal(r.get("prioritySendFlag"), "0");
                        prioritySendFlagArray[1] = (ComparisonPredicate)cb.equal(r.get("prioritySendFlag"), "1");
                        pre.getExpressions().add(cb.or(prioritySendFlagArray));
                    } else {
                        pre.getExpressions().add(cb.equal(r.get("prioritySendFlag"), psFlag));
                    }
                }
                
                return pre;
            }
        };
    }
    
    /**
     * 根据批次编号查找批次
     * 
     * @param workBatchCode
     *            批次编号
     * @return
     */
    public WorkBatch loadWorkBatchByCode(String workBatchCode) {
        WorkBatch batch = this.workBatchDao.loadbatchByBatchCode(workBatchCode);
        this.setDetail(batch);
        return batch;
    }
    
    /**
     * 设置属性
     * 
     * @param batch
     */
    private void setDetail(WorkBatch batch) {
        // customer
        String customerCode = batch.getCustomerCode();
        Customers customer = this.customersDao.findByCustomerCode(customerCode);
        batch.setCustomer(customer);
        
        // warehouse
        String warehouseCode = batch.getWarehouseCode();
        AreaGroup warehouse = this.areaGroupDao.findByGroupCode(warehouseCode);
        batch.setWarehouse(warehouse);
        
        // creater
        User user = this.userDao.findOne(batch.getCreaterId());
        batch.setCreater(user);
        
    }
    
    /**
     * 设置属性使用缓存
     * 
     * @param batch
     */
    private void setDetail(WorkBatch batch, Map<String, Object> cacheMap) {
        // customer
        String customerCode = batch.getCustomerCode();
        Customers customer = null;
        if (cacheMap.containsKey("customer_" + customerCode)) {
            customer = (Customers)cacheMap.get("customer_" + customerCode);
        } else {
            customer = this.customersDao.findByCustomerCode(customerCode);
            cacheMap.put("customer_" + customerCode, customer);
        }
        batch.setCustomer(customer);
        
        // warehouse
        String warehouseCode = batch.getWarehouseCode();
        AreaGroup warehouse = null;
        if (cacheMap.containsKey("areaGroup_" + warehouseCode)) {
            warehouse = (AreaGroup)cacheMap.get("areaGroup_" + warehouseCode);
        } else {
            warehouse = this.areaGroupDao.findByGroupCode(warehouseCode);
            cacheMap.put("areaGroup_" + warehouseCode, warehouse);
        }
        batch.setWarehouse(warehouse);
        
        // creater
        User user = null;
        if (cacheMap.containsKey("user_" + batch.getCreaterId())) {
            user = (User)cacheMap.get("user_" + batch.getCreaterId());
        } else {
            user = this.userDao.findOne(batch.getCreaterId());
            cacheMap.put("user_" + batch.getCreaterId(), user);
        }
        batch.setCreater(user);
        
    }
    
    /**
     * 绑定流转箱
     * 
     * @param taskCode
     * @param workBoxCode
     * @return success:绑定成功 busy:流转箱被占用 error:流转箱条码异常
     * @throws Exception
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public String sendBatch(String batchcode)
        throws Exception
    {
        try {
            WorkBatch workBatch = this.loadWorkBatchByCode(batchcode);
            if (null == workBatch) {
                return null;
            } else {
                Batch batch = new Batch();
                batch.setBox_num(1);
                batch.setCustomer_code(workBatch.getCustomerCode());
                batch.setBatch(workBatch.getBatchCode());
                batch.setEndpoints(workBatch.getWarehouseCode());
                batch.setShipped_time(TimeUtil.getNowDateString(workBatch.getCreateTime()));
                batch.setStartpoints("0755");
                List<Box> boxList = new ArrayList<Box>();
                List<Order> orderList = new ArrayList<Order>();
                List<WorkTask> workTasks = this.workTaskDao.loadTaskByBatchCode(workBatch.getBatchCode());
                
                for (WorkTask wt : workTasks) {
                    Box box = new Box();
                    box.setBox_code(wt.getTaskCode());
                    box.setBox_type(workBatch.getPickType());
                    box.setGoods_num(Integer.parseInt(wt.getGoodsNum() + ""));
                    box.setWeight(0);
                    
                    List<Box_detail> skuList = new ArrayList<Box_detail>();
                    List<WorkTaskItem> wtis = workTaskItemDao.loadByTaskCode(wt.getTaskCode());
                    for (WorkTaskItem wti : wtis) {
                        Box_detail sku = new Box_detail();
                        sku.setBox(box.getBox_code());
                        sku.setNum(wti.getPickedNum());
                        sku.setSku(wti.getSku());
                        skuList.add(sku);
                    }
                    box.setBox_detail(skuList);
                    boxList.add(box);
                }
                
                int sun_num = 0;
                List<WorkOrder> workOrders = this.workOrderDao.loadOrderByBatchCode(batchcode);
                for (WorkOrder wo : workOrders) {
                    Order order = new Order();
                    order.setOrdeno(wo.getOrder().getOrdeno());
                    order.setFromno(wo.getOrder().getFromno());
                    order.setOrder_type(workBatch.getPickType());
                    order.setSku_num(wo.getGoodsNum());
                    String expressNo = "";
                    if ("cbill".equals(wo.getBillType())) {
                        expressNo = wo.getExpressNo();
                    } else {
                        expressNo = wo.getEbillNo();
                    }
                    order.setExpressNo(expressNo);
                    order.setExpressCode(wo.getExpressCompany());
                    order.setExpressName(wo.getExpressName());
                    
                    Integer wi = wo.getWeight();
                    if (null == wi) {
                        wi = 0;
                    }
                    order.setWeight(wi);
                    sun_num += wo.getGoodsNum();
                    orderList.add(order);
                }
                batch.setGoods_num(sun_num);
                batch.setOrder_num(workBatch.getOrderQty());
                batch.setBox(boxList);
                batch.setOrder(orderList);
                ObjectMapper objectMapper = new ObjectMapper();
                return objectMapper.writeValueAsString(batch);
                /*
                 * try {
                 * String result = SystemUtil.pub(
                 * "http://tms.paiu.com.cn/tms/api/wms/batch", "appkey=123&appsecret=123&data=" + );
                 * JSONObject json2 = JSONObject.fromObject(result);
                 * String status= json2.getString("status");
                 * if("1".equals(status))
                 * {
                 * workBatch.setSyncStatus("finish");
                 * this.workBatchDao.updateSyncStatus(workBatch.getBatchCode(), workBatch.getSyncStatus());
                 * }
                 * return result;
                 * } catch (JsonGenerationException e) {
                 * // TODO Auto-generated catch block
                 * 
                 * } catch (JsonMappingException e) {
                 * // TODO Auto-generated catch block
                 * 
                 * } catch (IOException e) {
                 * // TODO Auto-generated catch block
                 * 
                 * }
                 * return json.toString();
                 */
            }
            
        } catch (Exception e) {
            
            return null;
        }
    }
    
    /**
     * 
     * 标记为优先派送
     *
     * @param ordernos
     * @return [参数说明]
     * @author <a href=mailto:zhangweize@nryuncang.com>zhangweize</a>
     * @version 1.0.0
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public boolean signPriorSend(String orderno)
        throws Exception
    {
        // 标记为优先派送
        int count = ediOrderDao.updateEdiOrderPrioritySendFlag("1", orderno);
        return count == 1 ? true : false;
        
    }
    
    /**
     * 
     * 
     * 确认分配快递
     * 
     * @param selectedOrder
     * @param psCompanyCode
     * @return
     * @throws Exception
     *             [参数说明]
     * @author <a href=mailto:zhangweize@nryuncang.com>zhangweize</a>
     * @version 1.0.0
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public String assignCompany(String selectedOrder, String psCompanyCode)
        throws Exception
    {
        String[] ordernos = selectedOrder.split(",");
        EdiOrder ediOrder = null;
        for (String orderno : ordernos) {
            if (StringUtils.isEmpty(orderno) || StringUtils.isEmpty(psCompanyCode)) {
                continue;
            }
            ediOrder = ediOrderDao.findByOrdeno(orderno);
            if (!"TTKDEX".equals(psCompanyCode)) {
                ediOrder.setPrioritySendFlag("0");
            }
            ediOrder.setCacode(psCompanyCode);
            ediOrderDao.save(ediOrder);
        }
        return "success";
    }
    
}